from flask import Flask, render_template, request, jsonify, send_file
import pandas as pd
import os
from fuzzywuzzy import fuzz
from datetime import datetime
import io

app = Flask(__name__)
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB max file size

class PriceComparisonTool:
    def __init__(self):
        self.inbound_df = None
        self.inquiry_df = None
        self.result_df = None
        self.unmatched_df = None
        self.matched_pairs = []
        
    def load_excel_files(self, inbound_file, inquiry_file):
        """加载Excel文件"""
        try:
            self.inbound_df = pd.read_excel(inbound_file)
            self.inquiry_df = pd.read_excel(inquiry_file)
            
            # 标准化列名
            self.inbound_df.columns = self.inbound_df.columns.str.strip()
            self.inquiry_df.columns = self.inquiry_df.columns.str.strip()
            
            return True, "文件加载成功"
        except Exception as e:
            return False, f"文件加载失败: {str(e)}"
    
    def standardize_product_names(self):
        """标准化商品名称"""
        if self.inbound_df is not None and '商品名' in self.inbound_df.columns:
            self.inbound_df['商品名_标准化'] = self.inbound_df['商品名'].apply(self._clean_product_name)
        
        if self.inquiry_df is not None and '商品名' in self.inquiry_df.columns:
            self.inquiry_df['商品名_标准化'] = self.inquiry_df['商品名'].apply(self._clean_product_name)
    
    def _clean_product_name(self, name):
        """清理商品名称"""
        if pd.isna(name):
            return ""
        
        # 移除多余空格和特殊字符
        import re
        name = str(name).strip()
        name = re.sub(r'\s+', ' ', name)  # 多个空格替换为一个
        name = re.sub(r'[^\w\s\-\(\)]', '', name)  # 保留字母数字中文括号连字符
        return name
    
    def compare_prices(self, fuzzy_threshold=80):
        """价格对比主函数"""
        if self.inbound_df is None or self.inquiry_df is None:
            return False, "请先加载数据文件"
        
        # 标准化商品名
        self.standardize_product_names()
        
        # 执行模糊匹配
        self._enhanced_name_matching(fuzzy_threshold)
        
        # 生成对比结果
        self._generate_comparison_results()
        
        return True, "价格对比完成"
    
    def _enhanced_name_matching(self, fuzzy_threshold=80):
        """增强商品名模糊匹配"""
        self.matched_pairs = []
        
        if not all(col in self.inbound_df.columns for col in ['商品名_标准化', '入库单价']):
            return
        
        if not all(col in self.inquiry_df.columns for col in ['商品名_标准化', '询价单价']):
            return
        
        # 创建副本用于匹配
        inbound_list = self.inbound_df[['商品名', '商品名_标准化', '入库单价']].copy()
        inquiry_list = self.inquiry_df[['商品名', '商品名_标准化', '询价单价']].copy()
        
        matched_inbound_indices = set()
        matched_inquiry_indices = set()
        
        for idx, inbound_row in inbound_list.iterrows():
            best_match = None
            best_score = 0
            
            for jdx, inquiry_row in inquiry_list.iterrows():
                if jdx in matched_inquiry_indices:
                    continue
                
                # 计算模糊匹配分数
                score = fuzz.ratio(inbound_row['商品名_标准化'], inquiry_row['商品名_标准化'])
                
                if score > best_score and score >= fuzzy_threshold:
                    best_score = score
                    best_match = jdx
            
            if best_match is not None:
                self.matched_pairs.append({
                    'inbound_idx': idx,
                    'inquiry_idx': best_match,
                    'inbound_name': inbound_row['商品名'],
                    'inquiry_name': inquiry